package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.LineItem;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.LineItemDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class LineItemDAOImpl implements LineItemDAO {

    //2.准备sql语句
    private static final String getLineItemsByOrderIdString = "SELECT ORDERID,LINENUM AS lineNumber,ITEMID,QUANTITY,UNITPRICE FROM LINEITEM WHERE ORDERID = ?";
    private static final String insertLineItemString = "INSERT INTO LINEITEM (ORDERID, LINENUM, ITEMID, QUANTITY, UNITPRICE)VALUES (?, ?, ?, ?, ?)";

    @Override
    public List<LineItem> getLineItemsByOrderId(int orderId) {
        List<LineItem> lineItemList = new ArrayList<LineItem>();
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(getLineItemsByOrderIdString);
            pStatement.setInt(1, orderId);
            //4.执行结果
            ResultSet resultSet = pStatement.executeQuery();

            while (resultSet.next()){
                LineItem lineItem = new LineItem();
                lineItem.setOrderId(resultSet.getInt(1));
                lineItem.setLineNumber(resultSet.getInt(2));
                lineItem.setItemId(resultSet.getString(3));
                lineItem.setQuantity(resultSet.getInt(4));
                lineItem.setUnitPrice(resultSet.getBigDecimal(5));
                lineItemList.add(lineItem);
            }

            DBUtil.closeResultSet(resultSet);//关闭结果集
            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
        return lineItemList;
    }

    @Override
    public void insertLineItem(LineItem lineItem) {
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(insertLineItemString);
            pStatement.setInt(1, lineItem.getOrderId());
            pStatement.setInt(2, lineItem.getLineNumber());
            pStatement.setString(3, lineItem.getItemId());
            pStatement.setInt(4, lineItem.getQuantity());
            pStatement.setBigDecimal(5, lineItem.getUnitPrice());
            pStatement.executeUpdate();

            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
    }
}
